﻿using QuickApp.Helpers;
using QuickApp.DbInterface;
using QuickApp.DbInterface.Helpers;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace QuickApp.SqlSilver
{
    public class SqlServerTable : QADbTable
    {
        #region Public Constructors

        public SqlServerTable(string name, string displayName, QADbManager dataManager, QADbDatabase parent)
            : base(name, displayName, dataManager, parent)
        {

        }

        #endregion Public Constructors

        #region Public Properties

        #endregion Public Properties

        #region Protected Methods

        protected override string GetDeleteCommand(DataRow dr)
        {
            StringBuilder where = new StringBuilder();
            foreach (DataColumn col in dr.Table.Columns)
            {
                where.Append(" AND " + col.ColumnName + " = " + formatTypeValue(col.DataType,dr[col]));
            }
            where.Remove(0, 5);
            return "DELETE FROM " + this.Name + " WHERE " + where.ToString();
        }

        protected override string GetInsertCommand(DataRow dr)
        {
            StringBuilder insert = new StringBuilder();
            StringBuilder values = new StringBuilder();
            foreach (DataColumn col in dr.Table.Columns)
            {
                insert.Append("," + col.ColumnName);
                values.Append("," + formatTypeValue(col.DataType,dr[col]));
            }
            insert.Remove(0, 1);
            values.Remove(0, 1);
            return "INSERT INTO " + this.Name + "(" + insert.ToString() + ") VALUES (" + values + ")";
        }
        protected override string GetSelectCommand(int numRows = 0)
        {
            string top = "TOP " + numRows;
            if (numRows == 0)
                top = string.Empty;
            return "SELECT " + top + " * FROM " + this.Name;
        }

        protected override string GetUpdateCommand(DataRow dr)
        {
            StringBuilder update = new StringBuilder();
            StringBuilder where = new StringBuilder();
            foreach (DataColumn col in dr.Table.Columns)
            {
                where.Append(" AND " + col.ColumnName + " = " + formatTypeValue(col.DataType,dr[col, DataRowVersion.Original]));
                if (!dr[col, DataRowVersion.Original].Equals(dr[col, DataRowVersion.Current]))
                    update.Append("," + col.ColumnName + " = " + formatTypeValue(col.DataType,dr[col]));
            }
            where.Remove(0, 5);
            if (update.Length == 0)
                return string.Empty;//Row was edited but nothing modified
            update.Remove(0, 1);
            return "UPDATE " + this.Name + " SET " + update.ToString() + " WHERE " + where.ToString();
        }

        protected override void LoadColumns()
        {
            base.columns = new List<QADbColumn>();
            foreach (DataRow dr in dataManager.SelectTable("SELECT COLUMN_NAME FROM " + parent.Name + ".INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + name + "' ").Rows)
                columns.Add(new SqlServerColumn(dr[0].ToString().EncloseInSquareBrackets(), dr[0].ToString(), dataManager, this));
        }

        #endregion Protected Methods

        #region Private Methods

        private string formatTypeValue(Type type, object value)
        {
            if(type.IsNumeric())
                return value.ToString();
            if (type == typeof(DateTime))
                return "'" + ((DateTime)value).ToString("yyyyMMdd HH:mm:ss.fff") + "'";
            else
                return "'" + value.ToString().Replace("'","''") + "'";
        }
        #endregion Private Methods

    }
}
